package com.olymtech.jbricks.jbricks.controller;

import com.olymtech.jbricks.jbricks.model.base_cityinfos;
import com.olymtech.jbricks.jbricks.model.base_fee_item;
import com.olymtech.jbricks.jbricks.model.base_freight_clause;
import com.olymtech.jbricks.jbricks.util.EasyPoiUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

/**
 * @author haoting.wang
 * @date 2020/10/30 9:54 上午
 **/

@RestController
@RequestMapping("generate")
public class GenerateSqlController {

    /**
     * 运输条款
     */
    public static String BASE_FREIGHT_CLAUSE = "insert into base_freight_clause(org_id,code,enname,cnname,is_enable,create_user_id,create_user_name,create_date) values ";

    public static String BASE_CITYINFOS = "insert into base_cityinfos(org_id,unique_code,code,en_name,cn_name,country_code,country_enname,country_cnname,is_enable,user_id,operator_name,create_time) values ";


    public static String BASE_FEE_ITEM = "insert into base_fee_item(dzg_id,unique_code,code,en_name,cn_name,tax_item_china,tax_type_num_china,is_enable,user_id,operator_name,create_time) values ";


    @RequestMapping("base_freight_clause")
    public String base_freight_clause(MultipartFile file) {

        List<base_freight_clause> list = EasyPoiUtil.importExcel(file, 0, 1, base_freight_clause.class);

        StringBuilder sb = new StringBuilder();

        sb.append(BASE_FREIGHT_CLAUSE);

        for (base_freight_clause data : list) {
            if (StringUtils.isEmpty(data.getCode())) {
                continue;
            }
            sb.append("(")
                    .append("\"").append(StringUtils.isEmpty(data.getOrgId()) ? "" : data.getOrgId()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getCode()) ? "" : data.getCode()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getEnname()) ? "" : data.getEnname()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getCnname()) ? "" : data.getCnname()).append("\"").append(",")
                    .append("\"").append("启用".equals(data.getIsEnable()) ? 1 : 2).append("\"").append(",")
                    .append("\"").append(data.getCreateUserId()).append("\"").append(",")
                    .append("\"").append(data.getCreateUserName()).append("\"").append(",")
                    .append("\"").append(DateFormatUtils.format(data.getCreateDate(), "yyyy-MM-dd hh:mm:ss")).append("\"").append(")")
                    .append(",");

        }

        int i = sb.lastIndexOf(",");
        sb.replace(i, i + 1, ";");

        return sb.toString();

    }

    @RequestMapping("base_cityinfos")
    public String base_cityinfos(MultipartFile file) {

        List<base_cityinfos> list = EasyPoiUtil.importExcel(file, 0, 1, base_cityinfos.class);

        StringBuilder sb = new StringBuilder();

        sb.append(BASE_CITYINFOS);

        for (base_cityinfos data : list) {
            if (StringUtils.isEmpty(data.getCode())) {
                continue;
            }
            sb.append("(")
                    .append("\"").append(StringUtils.isEmpty(data.getOrgId()) ? "" : data.getOrgId()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getUnicode()) ? "" : data.getUnicode()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getCode()) ? "" : data.getCode()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getEnname()) ? "" : data.getEnname()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getCnname()) ? "" : data.getCnname()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getCountryCode()) ? "" : data.getCountryCode()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getCountryEnName()) ? "" : data.getCountryEnName()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getCountryCnName()) ? "" : data.getCountryCnName()).append("\"").append(",")
                    .append("\"").append("启用".equals(data.getIsEnable()) ? 1 : 2).append("\"").append(",")
                    .append("\"").append(data.getCreateUserId()).append("\"").append(",")
                    .append("\"").append(data.getCreateUserName()).append("\"").append(",")
                    .append("\"").append(DateFormatUtils.format(data.getCreateDate(), "yyyy-MM-dd hh:mm:ss")).append("\"").append(")")
                    .append(",");

        }

        int i = sb.lastIndexOf(",");
        sb.replace(i, i + 1, ";");

        return sb.toString();

    }

    @RequestMapping("base_fee_item")
    public String base_fee_item(MultipartFile file) {

        List<base_fee_item> list = EasyPoiUtil.importExcel(file, 0, 1, base_fee_item.class);

        StringBuilder sb = new StringBuilder();

        sb.append(BASE_FEE_ITEM);

        for (base_fee_item data : list) {
            if (StringUtils.isEmpty(data.getCode())) {
                continue;
            }
            sb.append("(")
                    .append("\"").append(StringUtils.isEmpty(data.getOrgId()) ? "" : data.getOrgId()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getUnicode()) ? "" : data.getUnicode()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getCode()) ? "" : data.getCode()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getEnname()) ? "" : data.getEnname()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getCnname()) ? "" : data.getCnname()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getTaxItemChina()) ? "" : data.getTaxItemChina()).append("\"").append(",")
                    .append("\"").append(StringUtils.isEmpty(data.getTaxTypeNumChina()) ? "" : data.getTaxTypeNumChina()).append("\"").append(",")
                    .append("\"").append("启用".equals(data.getIsEnable()) ? 1 : 2).append("\"").append(",")
                    .append("\"").append(data.getCreateUserId()).append("\"").append(",")
                    .append("\"").append(data.getCreateUserName()).append("\"").append(",")
                    .append("\"").append(DateFormatUtils.format(data.getCreateDate(), "yyyy-MM-dd hh:mm:ss")).append("\"").append(")")
                    .append(",");

        }

        int i = sb.lastIndexOf(",");
        sb.replace(i, i + 1, ";");

        return sb.toString();

    }
}
